#include "salesanalysisdao.h"
#include "../database/database.h"
#include <QDebug>

SalesAnalysisDAO::SalesAnalysisDAO(Database *db, QObject *parent)
    : QObject(parent), m_db(db)
{
}

SalesAnalysisDAO::~SalesAnalysisDAO()
{
}

QVector<QMap<QString, QVariant>> SalesAnalysisDAO::getSalesChartData(const QDate &startDate, const QDate &endDate, bool isMonthly)
{
    QString query;
    QVector<QVariant> params;
    
    if (isMonthly) {
        // 按月统计销售数据
        query = "SELECT "
                "DATE_FORMAT(om.order_time, '%Y-%m') as time_period, "
                "SUM(om.final_amount) as sales_amount, "
                "SUM(od.subtotal - (od.quantity * p.purchase_price)) as gross_profit "
                "FROM order_main om "
                "INNER JOIN order_detail od ON om.order_id = od.order_id "
                "INNER JOIN product p ON od.product_id = p.product_id "
                "WHERE om.order_status = 1 "
                "AND DATE(om.order_time) >= ? "
                "AND DATE(om.order_time) <= ? "
                "GROUP BY DATE_FORMAT(om.order_time, '%Y-%m') "
                "ORDER BY time_period";
    } else {
        // 按日统计销售数据
        query = "SELECT "
                "DATE(om.order_time) as time_period, "
                "SUM(om.final_amount) as sales_amount, "
                "SUM(od.subtotal - (od.quantity * p.purchase_price)) as gross_profit "
                "FROM order_main om "
                "INNER JOIN order_detail od ON om.order_id = od.order_id "
                "INNER JOIN product p ON od.product_id = p.product_id "
                "WHERE om.order_status = 1 "
                "AND DATE(om.order_time) >= ? "
                "AND DATE(om.order_time) <= ? "
                "GROUP BY DATE(om.order_time) "
                "ORDER BY time_period";
    }
    
    params.append(startDate.toString("yyyy-MM-dd"));
    params.append(endDate.toString("yyyy-MM-dd"));
    
    return m_db->query(query, params);
}

QVector<QMap<QString, QVariant>> SalesAnalysisDAO::getCategorySalesData(const QDate &startDate, const QDate &endDate)
{
    QString query = "SELECT "
                   "c.category_id, "
                   "c.category_name, "
                   "SUM(od.subtotal) as sales_amount, "
                   "SUM(od.quantity) as sales_quantity, "
                   "COUNT(DISTINCT om.order_id) as order_count "
                   "FROM order_main om "
                   "INNER JOIN order_detail od ON om.order_id = od.order_id "
                   "INNER JOIN product p ON od.product_id = p.product_id "
                   "INNER JOIN category c ON p.category_id = c.category_id "
                   "WHERE om.order_status = 1 "
                   "AND DATE(om.order_time) >= ? "
                   "AND DATE(om.order_time) <= ? "
                   "GROUP BY c.category_id, c.category_name "
                   "ORDER BY sales_amount DESC";
    
    QVector<QVariant> params;
    params.append(startDate.toString("yyyy-MM-dd"));
    params.append(endDate.toString("yyyy-MM-dd"));
    
    return m_db->query(query, params);
}

QVector<QMap<QString, QVariant>> SalesAnalysisDAO::getStaffSalesData(const QDate &startDate, const QDate &endDate)
{
    QString query = "SELECT "
                   "u.user_id, "
                   "u.real_name as staff_name, "
                   "SUM(om.final_amount) as sales_amount, "
                   "COUNT(om.order_id) as order_count, "
                   "AVG(om.final_amount) as avg_order_amount "
                   "FROM order_main om "
                   "INNER JOIN user u ON om.user_id = u.user_id "
                   "WHERE om.order_status = 1 "
                   "AND DATE(om.order_time) >= ? "
                   "AND DATE(om.order_time) <= ? "
                   "GROUP BY u.user_id, u.real_name "
                   "ORDER BY sales_amount DESC";
    
    QVector<QVariant> params;
    params.append(startDate.toString("yyyy-MM-dd"));
    params.append(endDate.toString("yyyy-MM-dd"));
    
    return m_db->query(query, params);
}

QVector<QMap<QString, QVariant>> SalesAnalysisDAO::getPaymentMethodData(const QDate &startDate, const QDate &endDate)
{
    QString query = "SELECT "
                   "pm.method_id, "
                   "pm.method_name as payment_method, "
                   "SUM(om.final_amount) as total_amount, "
                   "COUNT(om.order_id) as transaction_count, "
                   "AVG(om.final_amount) as avg_transaction_amount "
                   "FROM order_main om "
                   "INNER JOIN payment_method pm ON om.method_id = pm.method_id "
                   "WHERE om.order_status = 1 "
                   "AND DATE(om.order_time) >= ? "
                   "AND DATE(om.order_time) <= ? "
                   "GROUP BY pm.method_id, pm.method_name "
                   "ORDER BY total_amount DESC";
    
    QVector<QVariant> params;
    params.append(startDate.toString("yyyy-MM-dd"));
    params.append(endDate.toString("yyyy-MM-dd"));
    
    return m_db->query(query, params);
}

QVector<QMap<QString, QVariant>> SalesAnalysisDAO::getCategoryDetailData(int categoryId, const QDate &startDate, const QDate &endDate)
{
    QString query = "SELECT "
                   "p.product_id, "
                   "p.product_name, "
                   "p.price as unit_price, "
                   "SUM(od.quantity) as total_quantity, "
                   "SUM(od.subtotal) as total_sales, "
                   "COUNT(DISTINCT om.order_id) as order_count "
                   "FROM order_main om "
                   "INNER JOIN order_detail od ON om.order_id = od.order_id "
                   "INNER JOIN product p ON od.product_id = p.product_id "
                   "WHERE om.order_status = 1 "
                   "AND p.category_id = ? "
                   "AND DATE(om.order_time) >= ? "
                   "AND DATE(om.order_time) <= ? "
                   "GROUP BY p.product_id, p.product_name, p.price "
                   "ORDER BY total_sales DESC";
    
    QVector<QVariant> params;
    params.append(categoryId);
    params.append(startDate.toString("yyyy-MM-dd"));
    params.append(endDate.toString("yyyy-MM-dd"));
    
    return m_db->query(query, params);
}